package com.wxg.sqlite;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.wxg.sqlite.util.Sql;

/**
 * 
 * @author pactera
 *
 */
public class EnglishWordDbInitialize {
	
	private static final Logger LOG = LoggerFactory.getLogger(EnglishWordDbInitialize.class);
	
	public static final String TB_ENGLISHWORD = "t_englishword";
	public static final String TB_WORDEXPLAIN = "t_wordexplain";
	
	/**
	 * Create table "t_englishword"
	 * 参考"https://www.sqlite.org/lang_createtable.html"
	 * 参考"http://www.runoob.com/sqlite/sqlite-create-table.html"
	 * 
	 */
	public void crTbEnglishword(){
		createTable(TB_ENGLISHWORD, sqlCreateTableEnglishword().str());
	}
	
	public void crTbWordexplain(){
		createTable(TB_WORDEXPLAIN, sqlCreateTableWordexplain().str());
	}
	
	private void createTable(String tableName, String createSql){
		try {
			if( SqliteUtils.verifyTableExist(tableName) ){
				if(LOG.isInfoEnabled()){
					LOG.info(String.format(" %s is exists!", tableName));
				}
			}else{
				LOG.info(String.format("Create table %s !", tableName));
				Connection conn = SqliteUtils.getConnection();
				Statement stmt = conn.createStatement();
				int result = stmt.executeUpdate(createSql);
				LOG.info(String.format(" createSql is %s ", createSql));
				LOG.info(String.format(" result is %s ", result));
				SqliteUtils.closeConnection(conn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public Sql sqlCreateTableEnglishword(){
		Sql sql = new Sql();
		sql.add(" CREATE TABLE ").heel(TB_ENGLISHWORD).heel(" (");
		sql.add(" id INTEGER PRIMARY KEY NOT NULL,");
		sql.add(" word TEXT NOT NULL,");
		sql.add(" usaPath TEXT,");
		sql.add(" britainPath TEXT,");
		sql.add(" createTime TEXT NOT NULL,");
		sql.add(" updateTime TEXT");
		sql.heel(")");
		return sql;
	}
	
	public Sql sqlCreateTableWordexplain(){
		Sql sql = new Sql();
		sql.add(" CREATE TABLE ").heel(TB_WORDEXPLAIN).heel(" (");
		sql.add(" id INTEGER PRIMARY KEY NOT NULL,");
		sql.add(" wordId INTEGER NOT NULL,");
		sql.add(" word TEXT NOT NULL,");
		sql.add(" explain TEXT");
		sql.heel(")");
		return sql;
	}
	
	public void dropTables(){
		try {
			Connection conn = SqliteUtils.getConnection();
			Statement stmt = conn.createStatement();
			{
				Sql sql = new Sql();
				sql.add("drop table ").heel(TB_ENGLISHWORD);
				int result = stmt.executeUpdate(sql.str());
				LOG.info(String.format(" sql is : %s", sql.str()));
				LOG.info(String.format(" result is %s ", result));
			}
			{
				Sql sql = new Sql();
				sql.add("drop table ").heel(TB_WORDEXPLAIN);
				int result = stmt.executeUpdate(sql.str());
				LOG.info(String.format(" sql is : %s", sql.str()));
				LOG.info(String.format(" result is %s ", result));
			}
			SqliteUtils.closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		EnglishWordDbInitialize ewdi = new EnglishWordDbInitialize();
		{
			ewdi.dropTables();
		}
		{
			ewdi.crTbEnglishword();
		}
		{
			ewdi.crTbWordexplain();
		}
	}

}
